const mysql = require("mysql2");

const dbConfig = {
  host: process.env.NODE_ENV_DB_HOST,
  port: process.env.NODE_ENV_DB_PORT_MYSQL,
  user: process.env.NODE_ENV_DB_USER,
  password: process.env.NODE_ENV_DB_PASSWORD,
  database: process.env.NODE_ENV_DB_DATABASE,
};

// 创建连接池
const pool = mysql
  .createPool({
    // 连接池数量
    connectionLimit: 1,
    ...dbConfig,
  })
  .promise();

async function query(sql, payload) {
  let res = null;
  let data = {};

  try {
    // 执行SQL语句
    res = await pool.query(sql, payload);
    data = {
      data: res[0],
      fields: res[1],
    };
  } catch (err) {
    console.log(err);
    switch (err.errno) {
      case 1054:
        data = {
          data: null,
          fields: null,
          error: "接口参数异常",
        };
        break;
    }
  }

  return data;
}

async function execute(sql, payload) {
  let data = null;

  // 执行SQL语句。支持预处理、支持防SQL注入
  const res = await pool.execute(sql, payload);

  return {
    data: res[0],
    fields: res[1],
  };
}

module.exports = {
  query,
  execute,
};
